﻿<cfsilent>
	<cfscript>

		event.setArg("pageTitle", "外语等级考试 - 本期报名情况");

		termId = event.getArg("TermID");
		
		/* 报名科目和年级分布状态 */
		sql = "SELECT 
				exam_id, t_class.grade, count(*) stu_count
				FROM t_cet_entry 
				INNER JOIN t_student ON t_cet_entry.stu_id = t_student.stu_id 
				INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
				WHERE 
					t_cet_entry.term_id = :termId 
				GROUP BY exam_id, t_class.grade 
				ORDER BY exam_id, t_class.grade ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );

		rs_stat = queryObj.execute( sql=sql ).getResult();

		cetArray = [];
		gradeArray = [];
		
		statStruct = {};
		
		structInsert( statStruct, "Sum", 0, true );

		for ( a=1; a LTE rs_stat.recordCount; a++ ) {

			if ( not arrayFind(cetArray, rs_stat["exam_id"][a] ) ) {
				arrayAppend( cetArray, rs_stat["exam_id"][a] );
				structInsert( statStruct, "Sum" & rs_stat["exam_id"][a], 0 );
			}
			if ( not arrayFind(gradeArray, rs_stat["grade"][a] ) ) {
				arrayAppend( gradeArray, rs_stat["grade"][a] );
				structInsert( statStruct, "Sum" & rs_stat["grade"][a], 0 );
			}
			
			structInsert( statStruct, rs_stat["exam_id"][a] & rs_stat["grade"][a], rs_stat["stu_count"][a] );
		}
		
		for ( cet in cetArray ) {
			for ( grade in gradeArray ) {
				
				if ( structKeyExists( statStruct, cet & grade ) ) {
					
					statStruct[ "Sum" & grade] += statStruct[cet & grade];
					statStruct[ "Sum" & cet] += statStruct[cet & grade];

					statStruct[ "Sum" ] += statStruct[cet & grade];
					
				}
				
			}
		}

		/* 缴费状态数据 */
		sql = "SELECT 
				exam_id, 
				count(*) stu_count, 
				sum( CASE WHEN is_charged = '1' THEN 1 ELSE 0 END ) charge_count, 
				sum( CASE WHEN substr(entry_prop, 2, 1) = '1' THEN 1 ELSE 0 END ) cache_count, 
				sum( CASE WHEN substr(entry_prop, 3, 1) = '1' THEN 1 ELSE 0 END ) bank_count, 
				sum( CASE WHEN substr(entry_prop, 4, 1) = '1' THEN 1 ELSE 0 END ) ebank_count 
				FROM t_cet_entry 
				WHERE 
					term_id = :termId 
				GROUP BY exam_id 
				ORDER BY exam_id ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );

		rs_charge = queryObj.execute( sql=sql ).getResult();
		

		chartColors = ['4572A7','AA4643','89A54E','80699B','3D96AE','DB843D','92A8CD','A47D7C','B5CA92','92B29A'];

		currentTab = event.getArg("TabID", "labChart");
		
		targetArgs = structNew();
		
		structInsert( targetArgs, "TID", termId, true);
		
	</cfscript>
</cfsilent>

<div id="headArea">
	<div class="uiHeader uiHeaderWithImage ptm">
		<div class="clearfix uiHeaderTop">
			<div class="uiHeaderActions rfloat">
				<a class="uiButton" href="<cfoutput>#buildURL('cetSetting')#</cfoutput>"><i class="mrs img btnPublish"></i><span class="uiButtonText">科目设置</span></a>
			</div>
			<div>
				<h2 class="uiHeaderTitle">
					<i class="uiHeaderImage img icon16x16 bar"></i>报名状态数据
				</h2>
			</div>
		</div>
	</div>
</div>

<div class="UIElement">
	<ul class="senateFirstPage">
		<li>
			<div class="clearfix" id="create">
				<div class="UItab">
					<div class="tabLables">
						<span <cfif currentTab eq "labChart">class="active"</cfif> id="labChart" tabTarget="Chart">分布情况</span>
						<span <cfif currentTab eq "labUpload">class="active"</cfif> id="labUpload" tabTarget="Upload">上传缴费数据</span>
						<span <cfif currentTab eq "labDownload">class="active"</cfif> id="labDownload" tabTarget="Download">下载报名数据</span>
					</div>
				</div>
				<script language="javascript" type="text/javascript">
					//<![CDATA[
					<!--
					$(document).ready(function() {
						
						/* init Tab */
						$("div.tabContent").hide();
						$("div#" + $("div.tabLables span.active").attr("tabTarget")).show();
						
						$("div.tabLables > span").click(function(){
							var $targetID = $(this).attr("tabTarget");
							
							$("div.tabLables span").removeClass("active");
							$(this).addClass("active");
							
							$("div.tabContent").not('[id="' + $targetID + '"]').hide();
							$("div.tabContent#" + $targetID).show();
						});
						
					});
					
					// -->
					//]]>
				</script>
				
				<div id="Chart" class="tabContent">
					
					<div class="noticeBlock">
						<h3>报名学生分布情况</h3>
						<p><span class="img icon16x16 message"></span>当前学期, 四六级共报名 <em><cfoutput>#statStruct["sum"]#</cfoutput></em> 人, 其中 <cfloop array="#cetArray#" index="cet"><b><cfoutput>#cet#</cfoutput></b> 报名 <em><cfif structKeyExists(statStruct, "sum"&cet)><cfoutput>#statStruct["sum"&cet]#</cfoutput><cfelse>0</cfif></em> 人 </cfloop>  </p>
						<hr/>
					</div>
					
					<table class="UItable">
						<tr class="cellBorder">
							<td>科目/年级</td>
							<cfloop array="#gradeArray#" index="grade">
							<td align="center"><span class="numeric"><cfoutput>#grade#</cfoutput></span></td>
							</cfloop>
							<td align="center">合计</td>
						</tr>
						<cfloop array="#cetArray#" index="cet">
							<tr class="cellBorder">
								<td><span class="numeric"><cfoutput>#cet#</cfoutput></span></td>
								<cfloop array="#gradeArray#" index="grade">
								<td align="center"><span class="numeric"><cfif structKeyExists(statStruct, cet&grade)><cfoutput>#statStruct[cet&grade]#</cfoutput><cfelse>0</cfif></span></td>
								</cfloop>
								<td align="center"><span class="numeric"><cfif structKeyExists(statStruct, "sum"&cet)><cfoutput>#statStruct["sum"&cet]#</cfoutput><cfelse>0</cfif></span>
							</tr>
						</cfloop>
						<tr class="cellBorder">
							<td>合计</td>
							<cfloop array="#gradeArray#" index="grade">
							<td align="center"><span class="numeric"><cfif structKeyExists(statStruct, "sum"&grade)><cfoutput>#statStruct["sum"&grade]#</cfoutput><cfelse>0</cfif></span></td>
							</cfloop>
							<td align="center"><span class="numeric bold"><cfoutput>#statStruct["sum"]#</cfoutput></span>
						</tr>
					</table>
					
					<div class="clearfix mtl mbl"></div>
					
					<div id="container" style="height:350px;"></div>
					
					<script language="javascript" type="text/javascript">
						//<![CDATA[
						<!--
						var chart;
						
						$(document).ready(function() {
							
							chart = new Highcharts.Chart({
								chart: {
									renderTo: 'container'
									},
								colors: [<cfset i = 0 /><cfloop array="#cetArray#" index="cet"><cfset i++ />'#<cfoutput>#chartColors[i]#</cfoutput>'<cfif i lt arrayLen(cetArray)>,</cfif></cfloop>],
								title: {
									text: null
									},
								xAxis: {
									categories: [ <cfset i = 0 /><cfloop array="#gradeArray#" index="grade"><cfset i++ />'<cfoutput>#grade#</cfoutput>'<cfif i lt arrayLen(gradeArray)>,</cfif></cfloop>]
									},
								yAxis: {
									title: {
										text: null
										}
									},
								tooltip: {
									borderRadius: 3,
									formatter: function() {
										var s;
										if (this.point.name) { // the pie chart
											s = '' + this.point.name +': '+ this.y +' 人';
										} else {
											s = '' + this.x  +'级: '+ this.y + '人报考';
										}
										return s;
									}
									},
								credits: {
									enabled: false
									},
								labels: {
									items: [{
										html: '报考科目比例',
										style: {
											left: '40px',
											top: '8px',
											color: 'black'
											}
										}]
									},
								series: [
									<cfloop array="#cetArray#" index="cet">
									{
									type: 'column',
									name: '<cfoutput>#cet#</cfoutput>',
									data: [<cfset i = 0 /><cfloop array="#gradeArray#" index="grade"><cfset i++ /><cfif structKeyExists(statStruct, cet&grade)><cfoutput>#statStruct[cet&grade]#</cfoutput><cfelse>0</cfif><cfif i lt arrayLen(gradeArray)>,</cfif></cfloop>]
									}
									,
									</cfloop>
									{
									type: 'spline',
									name: '平均报考人数',
									data: [<cfset i = 0 /><cfloop array="#gradeArray#" index="grade"><cfset i++ /><cfif structKeyExists(statStruct, "sum"&grade) and arrayLen(cetArray)><cfoutput>#int(statStruct["sum"&grade] / arrayLen(cetArray))#</cfoutput><cfelse>0</cfif><cfif i lt arrayLen(gradeArray)>,</cfif></cfloop>],
									marker: {
										lineWidth: 2,
										lineColor: '#92B29A',
										fillColor: 'white'
										}
									}, {
									type: 'pie',
									data: [
										<cfset i = 0 /><cfloop array="#cetArray#" index="cet"><cfset i++ />
										{
										name: '<cfoutput>#cet#</cfoutput>',
										y: <cfif structKeyExists(statStruct, "sum"&cet)><cfoutput>#statStruct["sum"&cet]#</cfoutput><cfelse>0</cfif>,
										color: '#<cfoutput>#chartColors[i]#</cfoutput>'
										}
										<cfif i lt arrayLen(cetArray)>,</cfif></cfloop>
										],
									center: [100, 80],
									size: 100,
									showInLegend: false,
									dataLabels: {
										enabled: false
										}
								}]
							});
						});
					
						// -->
						//]]>
					</script>
						
				</div>
				
				<div id="Upload" class="tabContent">
					
					<div class="noticeBlock">
						<h3>上传缴费数据</h3>
						<p><span class="img icon16x16 message"></span>请严格按照如下的数据模板在电子表格文件中录入学生缴费数据. 点击<a href="<cfoutput>#buildURL('importCETChargeTemplate')#</cfoutput>">下载学生缴费数据录入模板</a>, 在录入信息时请勿更改第一行表头内容.</p>
						<hr/>
						<table>
							<tr>
								<td>学号</td><td>姓名</td><td>报考科目</td><td>金额</td><td>缴费方式</td>
							</tr>
							<tr>
								<td>自行编定</td><td>学生姓名</td><td>CET4 / CET6 / CJT4 / CRT4</td><td>实际缴费金额</td><td>现金 / 银行代扣 / 网银</td>
							</tr>
						</table>
					</div>
					
					<form target="output" onSubmit="javascript:return processVerfiyForm('formImport');" id="formImport" class="formWrapper" method="post" enctype="multipart/form-data" action="<cfoutput>#buildURL('importCETChargeDo')#</cfoutput>">
						
						<div class="label">
							<span class="req">*</span><b>导入数据文件</b>
							<input name="upload" id="upload" type="file" verification="required" />
						</div>
						
						<div class="lable"><input id="importTrigger" class="button" type="submit" value="导入缴费数据" /></div>
					</form>
					
					<script language="javascript" type="text/javascript">
						//<![CDATA[
						<!--
						$(document).ready(function() {
							$("#importTrigger").click(function(){
								$("#output").attr("src","about:blank").removeClass("hidden_elem");
							});
						});
						// -->
						//]]>
					</script>

					<iframe name="output" id="output" class="UIframeBox hidden_elem" src="about:blank" frameborder="0"></iframe>
					
				</div>
				
				<div id="Download" class="tabContent">
					
					<div class="noticeBlock">
						<h3>下载报名数据</h3>
						<p><span class="img icon16x16 message"></span>此处下载的电子表格数据可以直接导入国家四六级报名管理系统, 请按照报考科目依次下载数据并尝试导入数据</p>
					</div>
					
					<table id="cetList" class="UIEditable">
						<thead>
							<tr>
								<td width="3"></td>
								<td width="80">报考科目</td>
								<td width="60">报考人数</td>
								<td width="60">缴费成功</td>
								<td width="60">现金</td>
								<td width="60">银行代扣</td>
								<td width="60">网银</td>
								<td colspan="3">报名相关资料下载</td>
							</tr>
						</thead>
						<tbody>
							<cfloop query="rs_charge">
							
							<cfset structInsert( targetArgs, "EX", rs_charge.exam_id, true ) />
							
							<tr class="editRows" rowid="<cfoutput>#rs_charge.exam_id#</cfoutput>">
								<td class="cetFlag <cfoutput>#rs_charge.exam_id#</cfoutput>"></td>
								<td><span class="numeric bold"><cfoutput>#rs_charge.exam_id#</cfoutput></span></td>
								<td><span class="numeric"><cfoutput>#rs_charge.stu_count#</cfoutput></span></td>
								<td><span class="numeric"><cfoutput>#rs_charge.charge_count#</cfoutput></span></td>
								<td><span class="numeric"><cfoutput>#rs_charge.cache_count#</cfoutput></span></td>
								<td><span class="numeric"><cfoutput>#rs_charge.bank_count#</cfoutput></span></td>
								<td><span class="numeric"><cfoutput>#rs_charge.ebank_count#</cfoutput></span></td>
								<td><a class="color excel" href="<cfoutput>#buildURL('unchargedDownloadExcel', targetArgs)#</cfoutput>"><span class="stat excel"></span>缴费清单</a></td>
								<td><a class="color access" href="<cfoutput>#buildURL('successDownloadExcel', targetArgs)#</cfoutput>"><span class="stat access"></span>报名数据</a></td>
								<td><a class="color zip" href="<cfoutput>#buildURL('cetPhotoDownload', targetArgs)#</cfoutput>"><span class="stat zip"></span>电子照片</a></td>
							</tr>
							</cfloop>
						</tbody>
					</table>
					
				</div>
				
			</div>
		</li>
	</ul>
</div>

